let express = require("express")();
const { response } = require("express");
let expressStatic = require("express-static");
const { request } = require("http");
let mysql = require("mysql");

//post请求需要的第三方模块
// let bodyParser = require('body-parser');
// express.use(bodyParser.json());
// express.use(bodyParser.urlencoded({extended: false}));
const port = 8800;

//规划数据库
let sql = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'studentmanage',
    timezone : "08:00"
})
sql.connect();

//接受到用户注册时请求时
express.get("/register",(request,response)=>{
    let p =request.query;
    sql.query(`INSERT INTO users (username,name,password,type) VALUES ("${p.username}","${p.name}","${p.password}","${p.type}")`,(error,data)=>{
        if(error){
            console.log(error);
        }
        else{
            response.send(data);
        }
    })   
})

//接受到用户登录时请求时
express.get("/login",(request,response)=>{
    let p =request.query;
    sql.query(`SELECT * FROM users WHERE username="${p.username}" AND password="${p.password}"`,(error,data)=>{
        //查询数据库失败
        if(error){
            console.log(error);
            response.send("error");
        }
        else{
            //账号和密码不匹配
            if(data.length === 0){
                console.log(data)
                response.send("验证失败");
            }
            else{
                console.log("haha");
                response.send(data);
            }  
        }
    })   
})


//后端收到前端的请求，返回数据库的所有学生信息
express.get("/getstudents",(request,response)=>{
    sql.query(`SELECT * FROM students ORDER BY id`,(error,data)=>{
        if(error){
            conslog.log(error);
        }
        else{
            response.send(data);
        }
    })
    
})

//后端收到删除的请求，执行的逻辑
express.get("/delect",(request,response)=>{
    sql.query(`DELETE FROM students WHERE id=${request.query.id}`,(error,data)=>{
        if(error){
            console.log(error)
        }
        else{
            response.send(data);
        }
    })
    
})

//后端收到添加学生的请求，执行的逻辑

express.get("/add",(request,response)=>{
    let m = request.query
    console.log(m)
    sql.query(`INSERT INTO students (name,age,sex,province,city,area,joindate) VALUES ("${m.name}","${m.age}","${m.sex}","${m.province}","${m.city}","${m.area}","${m.joindate}")`,(error,data)=>{
        if(error){
            console.log(error)
        }
        else{
            response.send("success");
        }
    })
    
})

//后端收到修改学生的请求，执行的逻辑
express.get("/updata",(request,response)=>{
    let m = request.query
    sql.query(`UPDATE students SET name = "${m.name}",age = "${m.age}",sex = "${m.sex}",province="${m.province}",city = "${m.city}",area="${m.area}",joindate = "${m.joindate}" WHERE id = "${m.id}"`,(error,data)=>{
        if(error){
            console.log(error)
        }
        else{
            response.send("success");
        }
    })    
})


//收到查看成绩的请求
express.get("/gainScore",(request,response)=>{
    let m = request.query
    sql.query(`SELECT students.name,students.id,grade.score,grade.subject FROM grade,students WHERE grade.studentId= ${m.stuId} AND students.id = grade.studentId`,(error,data)=>{
        if(error){
            console.log(error)
        }
        else{
            if(data.length === 0){
                response.send("0");
                console.log(data)
            }
            else{
                response.send(data);
                console.log("hah")
            }
        }  
    })    
})

express.use(expressStatic(__dirname + "/static"));
console.log(`running at ${port}`);
express.listen(port);
